using System.Data;
using System.Data.SqlClient;

namespace AspAlliance.SimpleCms
{
    public class DataHelper
    {
        public DataHelper(string connectionString)
        {
            _connectionString = connectionString;
        }

        private string _connectionString;

        public void SetupTable()
        {
            SqlHelper.ExecuteNonQuery(_connectionString, CommandType.Text, @"
                CREATE TABLE [dbo].[cms_Page](
	                [Id] [int] IDENTITY(1,1) PRIMARY KEY,
	                [Url] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	                [Title] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	                [Description] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	                [Keywords] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
                    [MasterPage] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	                [ContentText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	                [Enabled] [bit] NOT NULL CONSTRAINT [DF_cms_Page_Enabled]  DEFAULT ((1)),


	                ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

                CREATE UNIQUE INDEX UK_cms_Page2
                ON [dbo].[cms_Page] (Url)
");
        }

        public void TogglePageStatus(string url)
        {
            DataSet pageDataSet = SqlHelper.ExecuteDataset(_connectionString,
                CommandType.Text,
                "SELECT [Enabled] FROM [cms_Page] WHERE [Url] = @Url",
                new SqlParameter[] { 
                        new SqlParameter("@Url", url) 
                    });
            DataRow pageData = null;

            if (pageDataSet.Tables.Count > 0 && pageDataSet.Tables[0].Rows.Count > 0)
            {
                pageData = pageDataSet.Tables[0].Rows[0];
            }

            if (pageData != null)
            {
                SqlHelper.ExecuteNonQuery(_connectionString,
                    CommandType.Text,
                    "UPDATE [cms_Page] SET [Enabled] = @Enabled WHERE [Url] = @Url",
                    new SqlParameter[] { 
                            new SqlParameter("@Enabled", !(bool)pageData["Enabled"]), 
                            new SqlParameter("@Url", url) 
                        });
            }
        }

        public DataRow SelectPage(int pageId)
        {
            DataSet pageData = SqlHelper.ExecuteDataset(_connectionString,
                CommandType.Text,
                "SELECT * FROM [cms_Page] WHERE [Id] = @Id",
                new SqlParameter[] { new SqlParameter("@Id", pageId) });
            if (pageData.Tables.Count > 0 && pageData.Tables[0].Rows.Count > 0)
            {
                return pageData.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }

        public DataRow SelectPage(string url)
        {
            DataSet pageData = SqlHelper.ExecuteDataset(_connectionString, 
                CommandType.Text, 
                "SELECT * FROM [cms_Page] WHERE [Url] = @Url AND [Enabled] = 1", 
                new SqlParameter[] { new SqlParameter("@Url", url) });
            if (pageData.Tables.Count > 0 && pageData.Tables[0].Rows.Count > 0)
            {
                return pageData.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }

        public void UpdatePage(CmsPage myPage)
        {
            SqlHelper.ExecuteNonQuery(_connectionString,
                CommandType.Text,
                "UPDATE [cms_Page] SET [ContentText] = @ContentText, [Url] = @Url, [Title] = @Title, [Description] = @Description, [Keywords] = @Keywords, [MasterPage] = @MasterPage, [Enabled] = @Enabled WHERE [Id] = @Id",
                new SqlParameter[] { 
                    new SqlParameter("@ContentText", myPage.ContentText), 
                    new SqlParameter("@Url", myPage.Url), 
                    new SqlParameter("@Title", myPage.Title), 
                    new SqlParameter("@Description", myPage.Description), 
                    new SqlParameter("@Keywords", myPage.Keywords),
                    new SqlParameter("@MasterPage",myPage.MasterPage),
                    new SqlParameter("@Enabled", myPage.Enabled), 
                    new SqlParameter("@Id", myPage.Id) 
                });
        }

        public void InsertPage(CmsPage myPage)
        {
            SqlHelper.ExecuteNonQuery(_connectionString, 
                CommandType.Text, 
                "INSERT INTO [cms_Page] ([Url], [Title], [Description], [Keywords], [ContentText], [MasterPage], [Enabled]) VALUES (@Url, @Title, @Description, @Keywords, @ContentText, @MasterPage, @Enabled)", 
                new SqlParameter[] { 
                    new SqlParameter("@Url", myPage.Url), 
                    new SqlParameter("@Title", myPage.Title), 
                    new SqlParameter("@Description", myPage.Description), 
                    new SqlParameter("@Keywords", myPage.Keywords), 
                    new SqlParameter("@ContentText", myPage.ContentText),
                    new SqlParameter("@MasterPage", myPage.MasterPage),
                    new SqlParameter("@Enabled", myPage.Enabled) 
                });
        }

        public DataTable SelectAllPages()
        {
            DataSet allPages = SqlHelper.ExecuteDataset(_connectionString, 
                CommandType.Text, 
                "SELECT * FROM [cms_Page] ORDER BY [Url]");
            if (allPages.Tables.Count > 0)
            {
                return allPages.Tables[0];
            }
            else
            {
                return null;
            }
        }

        public DataTable SelectFolderPages(string path)
        {
            DataTable allPages = SqlHelper.ExecuteDataset(_connectionString,
                CommandType.Text,
                "SELECT [Id], [Url], [Title], [Enabled] FROM [cms_Page] WHERE [Url] LIKE '~/' + @Path + '/%' ORDER BY [Url]",
                new SqlParameter[] { 
                    new SqlParameter("@Path", path) 
                }).Tables[0];
            return allPages;
        }

    }
}
